<?php
namespace Weixin\Controller;

use Think\Controller;

class WxUserController extends Controller {
	
	public function index() {
		$wx_nickname = '';
		$openid = '';
		$wx_sex = '';
		$wx_country = '';
		$wx_province = '';
		$wx_city = '';
		$bindingPhone = '';
		$bindingName = '';
		$levelID = '';
		$queryCompCode = 'all';
		$areaName = 'all';
		$carNO = '';
		
		$where = array();
		
		
		
		if(!isset($_GET['queryCompCode'])){
			$queryCompCode = 'all';
		}else{
		
			$queryCompCode = $this->_get('queryCompCode');
			if($queryCompCode != 'all'){
				$where['compCode'] = $queryCompCode;
			}
		}
		
		
		if(!isset($_GET['areaName'])){
			$areaName = 'all';
		}else{
			$areaName = get_encoding($this->_get('areaName'), "UTF-8");
			$areaName = decodeMore($areaName);//被多次urlencode的处理
			
			if($areaName != 'all'){
				$where['areaName'] = $areaName;
			}
		}

		
		
		if(isset($_GET['carNO']) && !empty($_GET['carNO'])){
			$carNO = $this->_get('carNO');
// 			$carNO = get_encoding($this->_get('carNO'), "UTF-8");
			$carNO = decodeMore($carNO);
			$where['carNO'] = array('like','%'.$carNO.'%');
		}
		
		
		
		if(!empty($_GET['wx_nickname']) && isset($_GET['wx_nickname'])){
			$wx_nickname = $this->_get('wx_nickname');
// 			$wx_nickname = get_encoding($this->_get('wx_nickname'), "UTF-8");
			$wx_nickname = decodeMore($wx_nickname);
			$where['wx_nickname'] = array('like','%'.$wx_nickname.'%');
		}
		
		if(!empty($_GET['openid']) && isset($_GET['openid'])){
			$openid = $this->_get('openid');
			$openid = decodeMore($openid);
			$where['openid'] = array('like','%'.$openid.'%');
		}
		
		
		
		if(!empty($_GET['wx_sex']) && isset($_GET['wx_sex'])){
			$wx_sex = $this->_get('wx_sex');
			if($wx_sex == 1 || $wx_sex == 2){
				$where['wx_sex'] = $wx_sex;
			}
		}
		
		if(!empty($_GET['wx_country']) && isset($_GET['wx_country'])){
			$wx_country = $this->_get('wx_country');
			$where['wx_country'] = array('like','%'.$wx_country.'%');
		}
		
		
		if(!empty($_GET['wx_province']) && isset($_GET['wx_province'])){
			$wx_province = $this->_get('wx_province');
			$where['wx_province'] = array('like','%'.$wx_province.'%');
		}
		
		if(!empty($_GET['wx_city']) && isset($_GET['wx_city'])){
			$wx_city = $this->_get('wx_city');
			$where['wx_city'] = array('like','%'.$wx_city.'%');
		}
		if(!empty($_GET['bindingPhone']) && isset($_GET['bindingPhone'])){
			$bindingPhone = $this->_get('bindingPhone');
			$where['bindingPhone'] = array('like','%'.$bindingPhone.'%');
		}
		if(!empty($_GET['bindingName']) && isset($_GET['bindingName'])){
			$bindingName = $this->_get('bindingName');
			$where['bindingName'] = array('like','%'.$bindingName.'%');
		}
		
		if(!empty($_GET['levelID']) && isset($_GET['levelID'])){
			$levelID = get_encoding($this->_get('levelID'), "UTF-8");
			$levelID = decodeMore($levelID);//被多次urlencode的处理
			$where['levelID'] = array('like','%'.$levelID.'%');
		}
		

		

		import ( 'ORG.Util.Page' );
		$m = M ( 'wx_visitor' );
		if (!empty($where)) {
    		$count = $m->where($where)->count ();
    	}else{
    		$count = $m->count ();
    	}
		import ( 'ORG.Util.Page' );
		$page = new Page ( $count, 15 );
		$limit = $page->firstRow . ',' . $page->listRows;
		
		if (!empty($where)) {
			$list = $m->order ( 'id desc' )->where($where)->limit ( $limit )->select ();
		}else{
			$list = $m->order ( 'id desc' )->limit ( $limit )->select ();
		}
		
		foreach ($list as $k => $v){
			
			$carArr = explode('|', $v['carNO']);
			if(count($carArr)  > 0){
				$list[$k]['carNO'] = $carArr;
			}
			$ageCount = $this->ageCount($v['orderFirstTimes']);
			$list[$k]['ageCount'] = $ageCount;//预约的年龄
			if(empty($v['orderCount']) || empty($ageCount) || $ageCount == 0 || $v['orderCount'] == 0){
				$list[$k]['yearAvgCount'] = 0;
			}else{
				$list[$k]['yearAvgCount'] = $v['orderCount']/$ageCount;//预约的年龄
			}
			
			
		}
		
		
		
		$mStore = M('wx_store_address');
		$allStoreArr = $mStore->field('id,compCode,compName')->select();
		
		$areaArr = $mStore->Distinct(true)->field('areaName')->select();
	

		
		$this->assign('areaArr',$areaArr);
		
		$this->assign('allStoreArr',$allStoreArr);
		
		
		
		$this->assign ( 'wx_nickname', $wx_nickname );
		$this->assign ( 'openid', $openid );
		$this->assign ( 'wx_sex', $wx_sex );
		$this->assign ( 'wx_country', $wx_country );
		$this->assign ( 'wx_province', $wx_province );
		$this->assign ( 'wx_city', $wx_city );
		$this->assign ( 'bindingPhone', $bindingPhone );
		$this->assign ( 'bindingName', $bindingName );
		$this->assign ( 'levelID', $levelID );

		$this->assign('queryCompCode',$queryCompCode);
		$this->assign('areaName',$areaName);
		$this->assign('carNO',$carNO);
		
		$this->assign ( 'pageList', $list );
		$this->assign ( 'page', $page->show () );
		
		$this->display ( "admin_wx_user_index" );
	}
	

	
	
	/**
	 * 计算预约的年龄
	 * @param unknown $compAge
	 * @return number
	 */
	protected function ageCount($times){
		 
		if(!empty($times)){
			$t = $times;
			$common = (time()-strtotime($t));
			$a = floor($common/86400/360);    //整数年
			$b = floor($common/86400/30) - $a*12; //整数月
			$c = floor($common/86400) - $a*360 - $b*30;   //整数日
			$d = floor($common/86400);    //总的天数
			if($b > 0){
				$a = $a +1;
			}
			$compAge = $a;
		}else{
			$compAge = 0;
		}
		return $compAge;
	}
	
	/**
	 * ajax删除微信用户
	 */
	public function ajaxDel(){
		
		if(isset($_GET['id'])){
			
			$id = $this->_get('id');
			$visitor = M('wx_visitor')->where(array('id'=>$id))->delete();
			
			if ($visitor) {
				echo 1;//删除成功
				exit();
			}else{
				echo 2;//删除失败
				exit();
			}
		}else{
			echo 2;
		}
		
		
		
	}
	
	/**
	 * 详情
	 */
	public function detail(){
		
		if(isset($_GET['id'])){
			
			$id = $this->_get('id');
			$where = array('id'=>$id);
			$one = M('wx_visitor')->where($where)->find();
			
			
			$carArr = explode('|', $one['carNO']);
			if(count($carArr)  > 0){
				$one['carNO'] = $carArr;
			}
			$ageCount = $this->ageCount($one['orderFirstTimes']);
			$one['ageCount'] = $ageCount;//预约的年龄
			if(empty($one['orderCount']) || empty($ageCount) || $ageCount == 0 || $one['orderCount'] == 0){
				$one['yearAvgCount'] = $one['orderCount'];
			}else{
				$one['yearAvgCount'] = $one['orderCount']/$ageCount;//预约的年龄
			}
			
			$this->assign ( 'one', $one );

			
			$this->display();
			exit();
		}else{
			$this->error('参数错误!');
			exit();
		}
		
		
		
	}
	
	
	/**
	 * 用户所有的车
	 */
	public function userAllCar(){
		
		if(isset($_GET['clientCode'])){
			$clientCode = $this->_get('clientCode');
			// 查询会员所有车辆【下次保养里程】数据接口
			$urlCarInfo = C ( 'apiUserAllCarInfo' );
			$urlCarInfo = $urlCarInfo . $clientCode;
				
			$resultCarInfoArr = httpGet ( $urlCarInfo );
			$resultCarInfoArr = json_decode ( $resultCarInfoArr, true );
				
			if ($resultCarInfoArr ['resultCode'] == 200) {
				$allCar = $resultCarInfoArr['result'];
				foreach ($allCar as $k => $v){
					$allCar[$k]['Carplate_CN'] = $this->getCarplate($v['Carplate']);
					$allCar[$k]['CarLogo'] = $this->getCarLogo($v['Carplate']);
				}
				$this->assign('allCar',$allCar);
				$this->display('carList');
			}else{
				$this->error('查询不到用户车辆!');
			}
			
		}else{
			$this->error('查询用户所有车辆信息出错！请重试！');
			exit();
		}
		
	}
	
	/**
	 * 根据厂牌返回厂牌的中文名称
	 * @param unknown $Carplate
	 * @return unknown
	 */
	protected function getCarplate($Carplate){
		//奔驰benz，宝马bmw，奥迪audi，保时捷porsche
		$str = '';
		$Carplate = strtolower($Carplate);
		if ( $Carplate == 'porsche') {
			$str = '保时捷';
		}else if($Carplate == 'audi'){
			$str = '奥迪';
		}else if($Carplate == 'bmw'){
			$str = '宝马';
		}else if($Carplate == 'benz'){
			$str = '奔驰';
		}else{
			$str = $Carplate;
		}
		 
		return $str;
	}
	
	protected function getCarLogo($Carplate){
		//奔驰benz，宝马bmw，奥迪audi，保时捷porsche
		$str = '';
		$Carplate = strtolower($Carplate);
		if ( $Carplate == 'porsche') {
			$str = 'BSJ_2';
		}else if($Carplate == 'audi'){
			$str = 'AD_2';
		}else if($Carplate == 'bmw'){
			$str = 'BM_2';
		}else if($Carplate == 'benz'){
			$str = 'BC_2';
		}else{
			$str = 'LH_2';
		}
	
		return $str;
	}
	
	
	/**
	 * 微信用户行为统计分析
	 */
	public function wxUserBehavior(){

		$userActionModel = M('wx_user_action');
		
		$where = " u.openid = c.openid where c.openid is not null ";
		
		
		$countSql = "select c.openid from ".C('DB_PREFIX')."wx_user_action u left join ".C('DB_PREFIX')."wx_visitor c on   ".$where." group by u.openid ";
		
		$countArr = $userActionModel->query($countSql);
		
		$count = count($countArr);
		//如果大于300条记录就取300条,小于300即有多少取多少
		if ($count > 300) {
			$count = 300;
		}
		import ( 'ORG.Util.Page' );
		$page = new Page ( $count, 15 );
		$limit = $page->firstRow . ',' . $page->listRows;
		
		$sql = "select c.openid openid,c.wx_nickname nickname,c.bindingName bindingName,c.bindingPhone bindingPhone,count(*) count from ".C('DB_PREFIX')."wx_user_action u left join ".C('DB_PREFIX')."wx_visitor c on   ".$where." group by u.openid order by count desc limit ".$limit;
		
		
		
		
		$list = $userActionModel->query($sql);
		
		foreach ($list as $k => $v){
			$list[$k]['num'] = $page->firstRow+$k+1;//提名
		}
		
		$this->assign ( 'pageList', $list );
		$this->assign ( 'page', $page->show () );
		$this->display();
		
	}
	
	/**
	 * 微信用户行为统计分析导出
	 */
	public function  wxUserBehaviorExport(){
		import('ORG.PHPExcel');
	
		$userActionModel = M('wx_user_action');
		//只导出300条记录
		$where = " u.openid = c.openid where c.openid is not null ";
		
		$sql = "select c.openid openid,c.wx_nickname nickname,c.bindingName bindingName,c.bindingPhone bindingPhone,count(*) count from ".C('DB_PREFIX')."wx_user_action u left join ".C('DB_PREFIX')."wx_visitor c on   ".$where." group by u.openid order by count desc limit 300";
		
		$data = $userActionModel->query($sql);
	
	
	
	
		date_default_timezone_set('PRC');
		$now_time = date('Y-m-d H:i:s');
		$filename = $now_time.".xls";
		//生成报表
		error_reporting(0);
		$objPHPExcel = new PHPExcel();
	
		/*以下是一些设置 ，什么作者  标题啊之类的*/
		$objPHPExcel->getProperties()->setCreator("charles")
		->setLastModifiedBy("charles")
		->setTitle("微信用户行为统计分析数据")
		->setSubject("数据EXCEL导出")
		->setDescription("备份数据")
		->setKeywords("excel")
		->setCategory("result file");
		$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
	
		$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
	
		//设置文件标题的字体和颜色
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:F1');//合并单元格
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '微信用户行为统计分析数据');
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setSize(14);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setBold(true); //字体加粗
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	
		//设置第二行的字体颜色和字体大小
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A2:F2');//合并单元格
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getFont()->setSize(8);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	
		//设置列标题的字体
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:F3')->getFont()->setSize(10);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:F3')->getFont()->setBold(true); //字体加粗
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A3:F3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	
		//设置水平居中
		$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
	
		//设置宽度
		$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(7);//表示设置A这一列的宽度,以下一样
		$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(38);
		$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(35);
		$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(35);
		$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(35);
		$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);

	
	
	
		$objPHPExcel->setActiveSheetIndex(0)
		->setCellValue('A2', '微信用户行为统计分析汇总  时间:' .$now_time)
		->setCellValue('A3', '排名')
		->setCellValue('B3', 'openid')
		->setCellValue('C3', '昵称')
		->setCellValue('D3', '姓名')
		->setCellValue('E3', '电话')
		->setCellValue('F3', '总访问次数');
		 
		$num=0;
		$coun = 1;
	
		foreach($data as $k => $v){
			$num=$k+4;
			$objPHPExcel->setActiveSheetIndex(0)
			//Excel的第A列，uid是你查出数组的键值，下面以此类推
			->setCellValue('A'.$num, $coun)
			->setCellValue('B'.$num, $v['openid'])
			->setCellValue('C'.$num, $v['nickname'])
			->setCellValue('D'.$num, $v['bindingName'])
			->setCellValue('E'.$num, $v['bindingPhone'])
			->setCellValue('F'.$num, $v['count']);
	
			$coun ++;
		}
		$last = $num+1;
		if($last == 1) {
			$last = 4;
		}
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$last.':F'.$last);//合并单元格
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$last, '总计:'.($coun-1).'条数据');
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$last)->getFont()->setSize(12);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$last)->getFont()->setBold(true); //字体加粗
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$last)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$last)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$last)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	
		$objPHPExcel->getActiveSheet()->setTitle("微信用户行为统计分析数据表");
		$objPHPExcel->setActiveSheetIndex(0);
		ob_end_clean();
	
		header("Content-type: text/csv");//重要
		header('Content-Disposition: attachment;filename="'.$filename.'"');
		header('Cache-Control: must-revalidate, post-check=0,pre-check=0');
		header('Expires:0');
		header('Pragma:public');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;
	}
	
	
	/**
	 * 1-12月每月新增用户统计分析图
	 */
	public function everyMonthAddUserAnalyze(){
		 
		date_default_timezone_set('PRC');
		$year =  date('Y');
		$mouthArr = array();
		for($i=1;$i<13;$i++){
			if($i<10){
				$mouthArr[] = $year."-"."0".$i;
			}else{
				$mouthArr[] = $year."-".$i;
			}
	
		}
		$countArr = array();
		$visitorModel = M("wx_visitor");
		$allVisitorYear_12str = "[";
		foreach ($mouthArr as $k => $v){
			$sql = "SELECT count(*) count from ".C('DB_PREFIX')."wx_visitor where timesMonth = '".$v."'";
			$counts = $visitorModel->query($sql);
			$count = $counts[0]['count'];
			$countArr[] = $count;
			$allVisitorYear_12str .=$count.",";
		}
		 
		$allVisitorYear_12str = substr($allVisitorYear_12str, 0, strlen($allVisitorYear_12str)-1);
		$allVisitorYear_12str .= "]";
		 
		$this->assign('allVisitorYear_12str',$allVisitorYear_12str);
		$this->display();
	}
	
	
	
	/**
	 * 当前用户量区域（分店区域）排名
	 */
	public function everyAreaUserAnalyze(){
			

		$storeComment = M('wx_store_address');
		$visitorModel = M("wx_visitor");
		
		$areaArr = $storeComment->Distinct(true)->field('areaName')->select();

		
		$data = array();
		
		foreach ($areaArr as $k => $v){
			$sql = "SELECT count(*) count from ".C('DB_PREFIX')."wx_visitor where areaName = '".$v['areaName']."'";
			$counts = $visitorModel->query($sql);
			$counts[0]['areaName'] = $v['areaName'];
			
			$data[] = $counts[0];
		}
		
		$areaArrData = $this->array_sort($data,'count','desc');
		
		$this->assign('areaArrData',$areaArrData);
		
		$this->display();
	}
	
	
	/**
	 * 客户等级统计分析
	 */
	public function levelAnalyze(){
		
		$levelArr = array("普通客户","金牌客户","钻石客户","新客户");
		
		
		$visitorModel = M("wx_visitor");
		
		$normal = 0;//普通客户
		$gold = 0;//金牌客户
		$diamond = 0;//钻石客户
		$new = 0;//新客户
		
		
		
		foreach ($levelArr as $k => $v){
			
			$sql = "SELECT count(*) count from ".C('DB_PREFIX')."wx_visitor where levelID = '".$v."'";
			$counts = $visitorModel->query($sql);
			$counts[0]['areaName'] = $v['areaName'];
			
			if($v == '普通客户'){
				$normal = $counts[0]['count'];
			}else if($v == '金牌客户'){
				$gold = $counts[0]['count'];
			}else if($v == '钻石客户'){
				$diamond = $counts[0]['count'];
			}else if($v == '新客户'){
				$new = $counts[0]['count'];
			}
			
		}
		
		
		
		$this->assign('normal',$normal);
		$this->assign('gold',$gold);
		$this->assign('diamond',$diamond);
		$this->assign('new',$new);
		
		$this->display();
		
		
	}
	
	
	/**
	 * 二维数组按指定的键值排序
	 * @param unknown $array
	 * @param unknown $keys
	 * @param string $type
	 * @return string|Ambigous <multitype:, unknown>
	 */
	protected  function array_sort($array,$keys,$type='asc'){
		if(!isset($array) || !is_array($array) || empty($array)){
			return '';
		}
		if(!isset($keys) || trim($keys)==''){
			return '';
		}
		if(!isset($type) || $type=='' || !in_array(strtolower($type),array('asc','desc'))){
			return '';
		}
		$keysvalue=array();
		foreach($array as $key=>$val){
			$val[$keys] = str_replace('-','',$val[$keys]);
			$val[$keys] = str_replace(' ','',$val[$keys]);
			$val[$keys] = str_replace(':','',$val[$keys]);
			$keysvalue[] =$val[$keys];
		}
		asort($keysvalue); //key值排序
		reset($keysvalue); //指针重新指向数组第一个
		foreach($keysvalue as $key=>$vals) {
			$keysort[] = $key;
		}
		$keysvalue = array();
		$count=count($keysort);
		if(strtolower($type) != 'asc'){
			for($i=$count-1; $i>=0; $i--) {
				$keysvalue[] = $array[$keysort[$i]];
			}
		}else{
			for($i=0; $i<$count; $i++){
				$keysvalue[] = $array[$keysort[$i]];
			}
		}
		return $keysvalue;
	}
}